package com.pub.dbo.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

import com.pub.db.DBConnectionManager;
import com.pub.dbo.dao.CountryDAO;
import com.pub.dbo.domain.Country;


public class CountryImpl implements CountryDAO
{
	private static Logger logger;    
	private static DBConnectionManager dbcm;
	
	private String DBTag;
	
	static
	{
		logger 	= Logger.getLogger (CountryImpl.class.getName());
	}
	
	public CountryImpl(String DBTag)
	{
		this.DBTag 	= DBTag;
		
		dbcm 	= DBConnectionManager.getInstance(CountryImpl.class.getName());
	}

	@Override
	public int insert(Country opObject)
	{
		int id = 0;
		
		StringBuilder sbSql = new StringBuilder(100);
		
		Connection conn = null;
		Statement sm 	= null;
		ResultSet rs 	= null;
		
		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();
			
			sbSql.append("INSERT INTO country SET zipcode = ");
			sbSql.append(opObject.getZipcode());

			sbSql.append(", code = '");
			sbSql.append(opObject.getCode());

			sbSql.append(", currencySign = '");
			sbSql.append(opObject.getCurrencysign());

			sbSql.append(", currencyCode = '");
			sbSql.append(opObject.getCurrencycode());

			sbSql.append("', status = '");
			sbSql.append(opObject.getStatus());

			sbSql.append("', BEDBName = '");
			sbSql.append(opObject.getBEDBName());
			
			sbSql.append("', FEDBName = '");
			sbSql.append(opObject.getFEDBName());
			
			sbSql.append("', crawlerDBName = '");
			sbSql.append(opObject.getCrawlerDBName());

			sbSql.append("'");
		
			sm.executeUpdate(sbSql.toString(), Statement.RETURN_GENERATED_KEYS);
			rs = sm.getGeneratedKeys();
			if ( rs.next() ) {
				id = rs.getInt(1);
			}
		}catch(Exception e){
			id = 0;
			
			logger.error("error SQL : " + sbSql.toString() + ", Exception : " + e.getMessage());
		}finally{
			if(id <= 0){
				try {
					conn.rollback();
				} catch (SQLException e) {
					logger.error("object rollback to insert operation error.");
					e.printStackTrace();
				}
			}
			
			try{
				if(rs != null)
					rs.close();
			}catch(SQLException e){}
			
			try{
				if(sm != null)
					sm.close();
			}catch(SQLException e){}
			
			if(conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		return id;
	}

	@Override
	public int update(Country opObject) 
	{
		int updateCount = 0;
		
		StringBuilder sbSql = new StringBuilder(120);
		
		Connection conn = null;
		Statement sm 	= null;
		
		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();
			
			sbSql.append("INSERT INTO country SET zipcode = ");
			sbSql.append(opObject.getZipcode());

			sbSql.append(", code = '");
			sbSql.append(opObject.getCode());

			sbSql.append(", currencySign = '");
			sbSql.append(opObject.getCurrencysign());

			sbSql.append(", currencyCode = '");
			sbSql.append(opObject.getCurrencycode());

			sbSql.append(", timezone = '");
			sbSql.append(opObject.getTimezone());

			sbSql.append("', BEDBName = '");
			sbSql.append(opObject.getBEDBName());
			
			sbSql.append("', FEDBName = '");
			sbSql.append(opObject.getFEDBName());
			
			sbSql.append("', crawlerDBName = '");
			sbSql.append(opObject.getCrawlerDBName());
			
			sbSql.append("', status = '");
			sbSql.append(opObject.getStatus());

			sbSql.append("', id=");
			sbSql.append(opObject.getID());
			sbSql.append(" ON DUPLICATE KEY UPDATE status=VALUES(status), timezone=VALUES(timezone), zipcode=VALUES(zipcode), code=VALUES(code), currencySign=VALUES(currencySign), currencyCode=VALUES(currencyCode)");
			sbSql.append(", BEDBName=VALUES(BEDBName), FEDBName=VALUES(FEDBName), crawlerDBName=VALUES(crawlerDBName)");
			
			sm.executeUpdate(sbSql.toString());
		}catch(Exception e){
			if(conn != null){
				try {
					conn.rollback();
				} catch (SQLException e1) {
				}
			}

			logger.error("error SQL : " + sbSql.toString() + ", Exception : " + e.getMessage());
		}finally{
			try{
				if(sm != null)
					sm.close();
			}catch(SQLException e){}
			
			if(conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		return updateCount;
	}
	
	@Override
	public int delete(Country opObject)
	{
		if(opObject == null || opObject.getID() <= 0)
			return 0;
		
		int updateCount = 0;
		
		StringBuilder sbSql = new StringBuilder(120);
		
		Connection conn = null;
		Statement sm 	= null;
		
		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();
			
			sbSql.append("DELETE FROM country WHERE id=");
			sbSql.append(opObject.getID());
			updateCount = sm.executeUpdate(sbSql.toString());
		}catch(Exception e){
			if(conn != null){
				try {
					conn.rollback();
				} catch (SQLException e1) {
				}
			}

			logger.error("error SQL : " + sbSql.toString() + ", Exception : " + e.getMessage());
		}finally{
			try{
				if(sm != null)
					sm.close();
			}catch(SQLException e){}
			
			if(conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		return updateCount;
	}
	
	@Override
	public Country getCountry(String code)
	{
		Country opObject = null;
		
		Connection conn = null;
		Statement sm 	= null;
		ResultSet rs 	= null;
		
		StringBuilder sbSql = new StringBuilder(200);
		sbSql.append("SELECT id, zipcode, code, currencySign, currencyCode, timezone, timezoneOffset, createdTime, modifiedTime, language,");
		sbSql.append("status, isAdmin, isHome, isDefault, BEDBName, FEDBName, CrawlerDBName FROM country WHERE code='");

		sbSql.append(code);
		sbSql.append("'");
		
		try {
			conn 	= dbcm.getConnection(DBTag);
			sm		= conn.createStatement();
			rs 		= sm.executeQuery(sbSql.toString());
			
			if(rs.next()){
				opObject = new Country();
				
				opObject.setID(rs.getInt(1));
				opObject.setZipcode(rs.getInt(2));
				opObject.setCode(rs.getString(3));
				opObject.setCurrencysign(rs.getString(4));
				opObject.setCurrencycode(rs.getString(5));
				
				opObject.setTimezone(rs.getString(6));
				opObject.setTimezoneOffset(rs.getInt(7));
				
				opObject.setCreatedTime(rs.getString(8));
				opObject.setModifiedTime(rs.getString(9));
				
				opObject.setLanguage(rs.getString(10));
				
				opObject.setStatus(rs.getBoolean(11));
				opObject.setIsAdmin(rs.getBoolean(12));
				opObject.setIsHome(rs.getBoolean(13));
				opObject.setIsDefault(rs.getBoolean(14));
				
				opObject.setBEDBName(rs.getString(15));
				opObject.setFEDBName(rs.getString(16));
				opObject.setCrawlerDBName(rs.getString(17));
			}
		} catch (Exception e) {
			logger.error("error SQL : " + sbSql.toString() + ", Exception : " + e.getMessage());
		}finally{
			if(rs != null){
				try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			
			if(sm != null){
				try {
					sm.close();
				} catch (SQLException e) {
				}
			}
			
			if(conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		return opObject;
	}
	
	@Override
	public Country getCountry(int id)
	{
		Country opObject = null;
		
		Connection conn = null;
		Statement sm 	= null;
		ResultSet rs 	= null;
		
		StringBuilder sbSql = new StringBuilder(200);
		sbSql.append("SELECT id, zipcode, code, currencySign, currencyCode, timezone, timezoneOffset, createdTime, modifiedTime, language,");
		sbSql.append("status, isAdmin, isHome, isDefault FROM country WHERE id =");

		sbSql.append(id);
		
		try {
			conn 	= dbcm.getConnection(DBTag);
			sm		= conn.createStatement();
			rs 		= sm.executeQuery(sbSql.toString());
			
			if(rs.next()){
				opObject = new Country();
				
				opObject.setID(rs.getInt(1));
				opObject.setZipcode(rs.getInt(2));
				opObject.setCode(rs.getString(3));
				opObject.setCurrencysign(rs.getString(4));
				opObject.setCurrencycode(rs.getString(5));
				
				opObject.setTimezone(rs.getString(6));
				opObject.setTimezoneOffset(rs.getInt(7));
				
				opObject.setCreatedTime(rs.getString(8));
				opObject.setModifiedTime(rs.getString(9));
				
				opObject.setLanguage(rs.getString(10));
				
				opObject.setStatus(rs.getBoolean(11));
				opObject.setIsAdmin(rs.getBoolean(12));
				opObject.setIsHome(rs.getBoolean(13));
				opObject.setIsDefault(rs.getBoolean(14));
			}
		} catch (Exception e) {
			logger.error("error SQL : " + sbSql.toString() + ", Exception : " + e.getMessage());
		}finally{
			if(rs != null){
				try {
					rs.close();
				} catch (SQLException e) {
				}
			}
			
			if(sm != null){
				try {
					sm.close();
				} catch (SQLException e) {
				}
			}
			
			if(conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		return opObject;
	}
	
	@Override
	public void close() 
	{
		dbcm.release(CountryImpl.class.getName());
	}
}
